#!/usr/bin/env python3
"""
MySQL到KWDB迁移工具高级使用示例

本示例展示高级功能，包括批量处理、错误处理、自定义转换规则等。
"""

import os
import sys
import logging
import tempfile
from typing import List, Dict, Any

# 添加src目录到Python路径
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..', 'src'))

from parser import MySQLParser, ParsedStatement, StatementType
from converter import MySQLToKWDBConverter
from generator import KWDBSQLGenerator
from validator import MigrationValidator


class AdvancedMigrationTool:
    """高级迁移工具类"""
    
    def __init__(self, config: Dict[str, Any] = None):
        self.config = config or {}
        self.setup_logging()
        
        self.parser = MySQLParser()
        self.converter = MySQLToKWDBConverter(config)
        self.generator = KWDBSQLGenerator(config)
        self.validator = MigrationValidator(config)
        
        self.logger = logging.getLogger(__name__)
    
    def setup_logging(self):
        """设置日志"""
        log_level = getattr(logging, self.config.get('log_level', 'INFO').upper())
        logging.basicConfig(
            level=log_level,
            format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
        )
    
    def migrate_with_error_handling(self, input_file: str, output_file: str) -> Dict[str, Any]:
        """带错误处理的迁移"""
        results = {
            'success': False,
            'original_count': 0,
            'converted_count': 0,
            'errors': [],
            'warnings': []
        }
        
        try:
            # 解析
            self.logger.info("开始解析MySQL文件...")
            original_statements = self.parser.parse_file(input_file)
            results['original_count'] = len(original_statements)
            
            # 转换（带错误处理）
            self.logger.info("开始转换语法...")
            converted_statements = []
            
            for i, stmt in enumerate(original_statements):
                try:
                    converted_stmt = self.converter.convert_statement(stmt)
                    if converted_stmt:
                        converted_statements.append(converted_stmt)
                except Exception as e:
                    error_msg = f"转换第{i+1}个语句时出错: {e}"
                    self.logger.warning(error_msg)
                    results['errors'].append({
                        'statement_index': i + 1,
                        'statement_type': stmt.statement_type.value,
                        'error': str(e),
                        'sql_preview': stmt.original_sql[:100] + '...'
                    })
            
            results['converted_count'] = len(converted_statements)
            
            # 生成文件
            if converted_statements:
                self.logger.info("生成KWDB SQL文件...")
                success = self.generator.generate_sql_file(converted_statements, output_file)
                results['success'] = success
            
            return results
            
        except Exception as e:
            self.logger.error(f"迁移失败: {e}")
            results['errors'].append({'general_error': str(e)})
            return results
    
    def batch_migrate_by_type(self, input_file: str, output_dir: str) -> Dict[str, str]:
        """按语句类型分批迁移"""
        os.makedirs(output_dir, exist_ok=True)
        
        # 解析所有语句
        original_statements = self.parser.parse_file(input_file)
        
        # 按类型分组
        statements_by_type = {}
        for stmt in original_statements:
            stmt_type = stmt.statement_type
            if stmt_type not in statements_by_type:
                statements_by_type[stmt_type] = []
            statements_by_type[stmt_type].append(stmt)
        
        # 按类型生成文件
        output_files = {}
        
        for stmt_type, statements in statements_by_type.items():
            if not statements:
                continue
                
            # 转换语句
            converted_statements = self.converter.convert_statements(statements)
            
            # 生成文件
            filename = f"{stmt_type.value.lower()}.sql"
            output_file = os.path.join(output_dir, filename)
            
            success = self.generator.generate_sql_file(converted_statements, output_file)
            
            if success:
                output_files[stmt_type.value] = output_file
                self.logger.info(f"生成{stmt_type.value}文件: {output_file}")
        
        return output_files
    
    def migrate_with_custom_rules(self, input_file: str, output_file: str, 
                                 custom_rules: Dict[str, Any]) -> bool:
        """使用自定义规则迁移"""
        # 创建带自定义规则的转换器
        config = {**self.config, **custom_rules}
        custom_converter = MySQLToKWDBConverter(config)
        
        # 解析和转换
        original_statements = self.parser.parse_file(input_file)
        converted_statements = custom_converter.convert_statements(original_statements)
        
        # 生成文件
        return self.generator.generate_sql_file(converted_statements, output_file)


def demonstrate_error_handling():
    """演示错误处理功能"""
    print("=== 错误处理示例 ===\n")
    
    # 创建包含错误的测试SQL
    problematic_sql = """
    CREATE DATABASE test_db;
    
    -- 这个语句有语法问题
    CREATE TABLE bad_table (
        id INT PRIMARY KEY,
        data SOME_UNKNOWN_TYPE
    );
    
    -- 正常的语句
    CREATE TABLE good_table (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    -- 另一个有问题的语句
    INSERT INTO non_existent_table VALUES (1, 'test');
    """
    
    # 写入临时文件
    with tempfile.NamedTemporaryFile(mode='w', suffix='.sql', delete=False) as f:
        f.write(problematic_sql)
        temp_file = f.name
    
    try:
        # 使用高级迁移工具
        config = {'log_level': 'DEBUG'}
        tool = AdvancedMigrationTool(config)
        
        results = tool.migrate_with_error_handling(temp_file, 'error_test_output.sql')
        
        print(f"迁移结果:")
        print(f"  原始语句数: {results['original_count']}")
        print(f"  转换成功数: {results['converted_count']}")
        print(f"  错误数: {len(results['errors'])}")
        print(f"  整体成功: {results['success']}")
        
        if results['errors']:
            print("\n错误详情:")
            for error in results['errors']:
                print(f"  - 语句{error.get('statement_index', '?')}: {error.get('error', 'Unknown error')}")
                if 'sql_preview' in error:
                    print(f"    SQL预览: {error['sql_preview']}")
        
    finally:
        os.unlink(temp_file)


def demonstrate_batch_migration():
    """演示分批迁移功能"""
    print("\n=== 分批迁移示例 ===\n")
    
    # 使用示例数据文件
    input_file = "../tests/sample_data/mysql_dump.sql"
    output_dir = "batch_output"
    
    if not os.path.exists(input_file):
        print(f"示例文件不存在: {input_file}")
        return
    
    tool = AdvancedMigrationTool()
    output_files = tool.batch_migrate_by_type(input_file, output_dir)
    
    print("分批迁移完成，生成的文件:")
    for stmt_type, file_path in output_files.items():
        file_size = os.path.getsize(file_path)
        print(f"  {stmt_type}: {file_path} ({file_size} bytes)")


def demonstrate_custom_rules():
    """演示自定义转换规则"""
    print("\n=== 自定义转换规则示例 ===\n")
    
    # 自定义规则
    custom_rules = {
        'type_mapping': {
            'LONGTEXT': 'CLOB',  # 自定义映射
            'MEDIUMTEXT': 'TEXT',
            'ENUM': 'VARCHAR(50)',  # 将ENUM转换为VARCHAR
        },
        'syntax_rules': {
            'AUTO_INCREMENT': 'GENERATED ALWAYS AS IDENTITY',  # 自定义自增语法
            'ENGINE=MyISAM': '-- MyISAM engine removed',
            'COMMENT': '-- Comment:'
        }
    }
    
    # 测试SQL
    test_sql = """
    CREATE TABLE custom_test (
        id INT AUTO_INCREMENT PRIMARY KEY,
        content LONGTEXT,
        status ENUM('active', 'inactive'),
        description MEDIUMTEXT
    ) ENGINE=MyISAM COMMENT='Test table';
    """
    
    # 写入临时文件
    with tempfile.NamedTemporaryFile(mode='w', suffix='.sql', delete=False) as f:
        f.write(test_sql)
        temp_file = f.name
    
    try:
        tool = AdvancedMigrationTool()
        success = tool.migrate_with_custom_rules(
            temp_file, 'custom_output.sql', custom_rules
        )
        
        if success:
            print("自定义规则迁移成功!")
            print("\n转换后的SQL:")
            with open('custom_output.sql', 'r') as f:
                print(f.read())
        else:
            print("自定义规则迁移失败")
            
    finally:
        os.unlink(temp_file)


def demonstrate_advanced_validation():
    """演示高级验证功能"""
    print("\n=== 高级验证示例 ===\n")
    
    # 创建模拟的语句列表
    statements = [
        ParsedStatement(
            statement_type=StatementType.CREATE_TABLE,
            original_sql="CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));",
            parsed_sql=None,
            table_name="users",
            columns=[
                {'name': 'id', 'type': 'INT', 'nullable': False, 'auto_increment': False},
                {'name': 'name', 'type': 'VARCHAR', 'nullable': True, 'auto_increment': False}
            ]
        ),
        ParsedStatement(
            statement_type=StatementType.CREATE_TABLE,
            original_sql="CREATE TABLE orders (id INT, user_id INT);",  # 缺少主键
            parsed_sql=None,
            table_name="orders",
            columns=[
                {'name': 'id', 'type': 'INT', 'nullable': True, 'auto_increment': False},
                {'name': 'user_id', 'type': 'INT', 'nullable': True, 'auto_increment': False}
            ]
        )
    ]
    
    validator = MigrationValidator()
    validation_results = validator.validate_migration(statements, statements)
    
    print("验证结果:")
    for i, result in enumerate(validation_results, 1):
        status = "✅ 通过" if result.is_valid else "❌ 失败"
        print(f"  {i}. {status}: {result.message}")
        
        if result.details:
            for key, value in result.details.items():
                print(f"     {key}: {value}")


def demonstrate_performance_optimization():
    """演示性能优化技巧"""
    print("\n=== 性能优化示例 ===\n")
    
    # 配置优化选项
    optimized_config = {
        'migration': {
            'batch_size': 5000,  # 大批量处理
            'skip_errors': True,  # 跳过错误继续处理
        },
        'logging': {
            'level': 'WARNING'  # 减少日志输出
        }
    }
    
    print("性能优化配置:")
    print(f"  批量大小: {optimized_config['migration']['batch_size']}")
    print(f"  跳过错误: {optimized_config['migration']['skip_errors']}")
    print(f"  日志级别: {optimized_config['logging']['level']}")
    
    # 创建优化的工具实例
    tool = AdvancedMigrationTool(optimized_config)
    
    print("\n优化建议:")
    print("1. 对于大型数据库，先迁移表结构，再迁移数据")
    print("2. 索引可以在数据导入后再创建")
    print("3. 使用批量处理减少事务开销")
    print("4. 在非高峰期执行迁移")
    print("5. 监控系统资源使用情况")


if __name__ == '__main__':
    # 演示错误处理
    demonstrate_error_handling()
    
    # 演示分批迁移
    demonstrate_batch_migration()
    
    # 演示自定义规则
    demonstrate_custom_rules()
    
    # 演示高级验证
    demonstrate_advanced_validation()
    
    # 演示性能优化
    demonstrate_performance_optimization()
    
    print("\n=== 所有示例演示完成 ===")
    print("生成的文件请查看当前目录和batch_output目录")
